package cn.com.nes.site.module.dao.crm;


import java.math.BigDecimal;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;
import org.springframework.transaction.TransactionStatus;

import cn.com.nes.site.entity.DataReq;
import cn.com.nes.site.entity.PageData;
import cn.com.nes.site.module.dao.BaseDaoImpl;


@Repository
public class AnalysisCusDaoImpl extends BaseDaoImpl {
	private TransactionStatus newg_status = null;

	
	//客户总体分析
	public PageData getTotalCustomer(DataReq req) {
		PageData res = new PageData();
		StringBuffer sb = new StringBuffer();
		Map<String,Object> extData = new LinkedHashMap<String, Object>();
		try {
			
			//线索目列表
			sb.delete(0, sb.length()) ;
			sb.append(" select sum(totalcus) totalcus,if(sum(nearlyThreeMonth) is null ,0,sum(nearlyThreeMonth)) nearlyThreeMonth,sum(oppcnt) oppcnt,if(round(sum(dealcnt)/sum(totalcus)*100,2) is null ,0,round(sum(dealcnt)/sum(totalcus)*100,2)) dealcnt,sum(contractcnt) contractcnt FROM ( ") ;
            sb.append(" SELECT count(*) totalcus ,sum(if(a.createTime>DATE_SUB(now(),INTERVAL 3 MONTH),1,0)) nearlyThreeMonth ,0 oppcnt,0 dealcnt,0 contractcnt  from customer a ") ;
			sb.append(" left join t_user b on a.createUid=b.id  where  a.isdelete=0 and b.companyId like concat(?,'%') ") ;
			sb.append(" union all ") ;
			sb.append(" SELECT 0 totalcus,0 nearlyThreeMonth,count(*)  oppcnt ,0 dealcnt,0 contractcnt from opportunity a INNER JOIN customer c on a.customerId=c.uuid  and c.isdelete='0' ") ;
			sb.append(" left join t_user b on a.createUid=b.id  where  a.isdelete=0 and b.companyId like concat(?,'%')  ") ;
			sb.append(" union all ") ;
			sb.append(" SELECT 0 totalcus,0 nearlyThreeMonth,0 oppcnt,count(distinct a.customerId)  dealcnt,0 contractcnt  from opportunity a INNER JOIN customer c on a.customerId=c.uuid and a.opportunitystage='06' and c.isdelete='0' ") ;
			sb.append(" left join t_user b on a.createUid=b.id  where  a.isdelete=0 and b.companyId like concat(?,'%')  ") ;
			sb.append(" ) d ") ;
			List<Map<String, Object>> list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId(),req.getUinfo().getCompanyId(),req.getUinfo().getCompanyId() });
			extData.put("totalCustomer", list.get(0)) ;
			
			res.setExtData(extData);		
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	//查询客户地区分布
	public PageData getCustomerDistribution(DataReq req) {
		PageData res = new PageData();
		StringBuffer sb = new StringBuffer();
		Map<String,Object> extData = new LinkedHashMap<String, Object>();
		try {
			//明细
			sb.delete(0, sb.length()) ;
			sb.append(" SELECT a.uuid,a.customernature ,c.name customernaturename ,a.businesstype,d.name businesstypename,customerlevel,e.name customerlevelname,a.customername,a.address,a.personincharge,a.phone,a.longitude,a.latitude,a.xzqhdm,f.xzqhmc from customer  a ") ;
			sb.append(" LEFT JOIN t_param c on a.customernature=c.`value` AND c.param_type='20' ") ;
			sb.append(" LEFT JOIN t_param d on a.businesstype=d.`value` AND d.param_type='21' ") ;
			sb.append(" LEFT JOIN t_param e on a.customerlevel=e.`value` AND e.param_type='22' ") ;
			sb.append(" INNER JOIN t_xzqh f on a.xzqhdm=f.xzqhdm ") ;
			sb.append(" left join t_user b on a.createUid=b.id  where  a.isdelete=0 and a.longitude is not null  and a.latitude is not null and  b.companyId like concat(?,'%')  ") ;
		    List<Map<String, Object>> list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId()});
		    Iterator<Map<String, Object>> iterator = list.iterator();
		    while(iterator.hasNext()){
		    	BigDecimal[] lnglat = new BigDecimal[2];
		    	Map<String, Object> next = iterator.next();
		    	BigDecimal lng = (BigDecimal) next.get("longitude");
		    	BigDecimal lat = (BigDecimal) next.get("latitude");
		    	lnglat[0] = lng;
		    	lnglat[1] = lat;
		    	next.put("lnglat", lnglat);
		    }
			extData.put("CustomerList", list) ;
			
			//合计
			sb.delete(0, sb.length()) ;
			sb.append(" SELECT  SUBSTR(a.xzqhdm ,1,9),f.xzqhmc ,count(*) cnt from customer a  ") ;
			sb.append(" INNER  JOIN t_xzqh f on CONCAT(SUBSTR(a.xzqhdm ,1,9),'000')=f.xzqhdm ") ;
			sb.append(" left join t_user b on a.createUid=b.id  where  a.isdelete=0  and b.companyId like concat(?,'%')  ") ;
			sb.append(" GROUP BY SUBSTR(a.xzqhdm ,1,9)  order by cnt desc ") ;
			list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			extData.put("CustomerSum", list) ;
			
			res.setExtData(extData);		
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
	//查询客户级别占比
	public PageData getCustomerLevelPer(DataReq req) {
		PageData res = new PageData();
		StringBuffer sb = new StringBuffer();
		Map<String,Object> extData = new LinkedHashMap<String, Object>();
		try {
		    //客户级别
			sb.delete(0, sb.length()) ;
			sb.append(" SELECT customerlevel,e.name customerlevelname,count(*) cnt from customer  a  ") ;
			sb.append(" INNER JOIN t_param e on a.customerlevel=e.`value` AND e.param_type='22' ") ;
			sb.append(" INNER JOIN t_user b on a.createUid=b.id  and b.sfsc=0  where  a.isdelete=0 and b.companyId like concat(?,'%')  ") ;
			sb.append(" GROUP BY customerlevel ") ;
			List<Map<String, Object>> list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId()});
			extData.put("customerlevel", list) ;
			
			//行业占比
			sb.delete(0, sb.length()) ;
			sb.append(" SELECT businesstype,e.name businesstypename,count(*) cnt from customer  a  ") ;
			sb.append(" INNER JOIN t_param e on a.businesstype=e.`value` AND e.param_type='21' ") ;
			sb.append(" INNER JOIN t_user b on a.createUid=b.id and b.sfsc=0  where  a.isdelete=0 and b.companyId like concat(?,'%')   ") ;
			sb.append(" GROUP BY businesstype ") ;
			list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			extData.put("businesstype", list) ;
			
			//客户性质
			sb.delete(0, sb.length()) ;
			sb.append(" SELECT a.customernature ,e.name customernaturename,count(*) cnt from customer  a   ") ;
			sb.append(" INNER JOIN t_param e on a.customernature=e.`value` AND e.param_type='20' ") ;
			sb.append(" INNER JOIN t_user b on a.createUid=b.id and b.sfsc=0  where  a.isdelete=0 and b.companyId like concat(?,'%')   ") ;
			sb.append(" GROUP BY customernature ") ;
			list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			extData.put("customernature", list) ;
			
			res.setExtData(extData);		
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
	//签单区域排行
	public PageData getCustomerOpp(DataReq req) {
		PageData res = new PageData();
		StringBuffer sb = new StringBuffer();
		Map<String,Object> extData = new LinkedHashMap<String, Object>();
		try {
		    //签单区域排行
			sb.delete(0, sb.length()) ;
			sb.append(" SELECT  f.xzqhmc ,COUNT(*) cnt from customer a  ") ;
			sb.append(" INNER JOIN t_xzqh f on CONCAT(SUBSTR(a.xzqhdm ,1,9),'000')=f.xzqhdm ") ;
			sb.append(" INNER JOIN opportunity g on g.customerId=a.uuid and opportunitystage='06'  ") ;
			sb.append(" left join t_user b on a.createUid=b.id  where  a.isdelete=0 and b.companyId like concat(?,'%')  ") ;
			sb.append(" GROUP BY f.xzqhmc ORDER BY cnt desc ") ;
			List<Map<String, Object>> list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId()});
			extData.put("CustomerOpp", list) ;
			
			res.setExtData(extData);		
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	
	//客户平均拜访时长
	public PageData getCustomerClaim(DataReq req) {
		PageData res = new PageData();
		StringBuffer sb = new StringBuffer();
		Map<String,Object> extData = new LinkedHashMap<String, Object>();
		try {
		    //客户平均拜访时长
			sb.delete(0, sb.length()) ;
			sb.append(" select sum(if(n.avgvit<=7,1,0)) WeekNum,round(sum(if(n.avgvit<=7,1,0))/count(*)*100,2) WeekNumPer,   sum(if(n.avgvit>7 and n.avgvit<=30,1,0)) MonthNum  ,round(sum(if(n.avgvit>7 and n.avgvit<=30,1,0))/count(*)*100,2) MonthNumPer  ,sum(if(n.avgvit>30 and n.avgvit<=365,1,0)) YearNum   ,round(sum(if(n.avgvit>30 and n.avgvit<=365,1,0))/count(*)*100,2) YearNumPer    ,sum(if(n.avgvit>365,1,0)) YearOver ,round(sum(if(n.avgvit>365,1,0))/count(*)*100,2) YearOverPer   ,count(*) totalCustomNum ") ;
			sb.append(" from (SELECT m.cid,round(avg(DATEDIFF(m.vt,IF (m.cid != m.ocid ,m.createTime,m.ovt))),2) avgvit ") ;
			sb.append(" from (SELECT	a.customerId cid,@oldcustomerId ocid,	a.visittime vt ,@oldtime ovt,	@oldcustomerId := a.customerId,@oldtime := a.visittime,a.createTime ") ;
			sb.append(" FROM	(SELECT c.customerId,c.visittime,d.createTime from log_claim c,customer d ,t_user s  where c.customerId=d.uuid and d.createUid=s.id and s.companyId  like concat(?,'%')  and s.sfsc=0 and c.isdelete=0 and d.isdelete=0 ORDER BY c.customerId,c.visittime ) a ") ;
			sb.append(" ,(SELECT @oldcustomerId:='') c) m GROUP BY cid) n ") ;
			List<Map<String, Object>> list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {req.getUinfo().getCompanyId()});
			extData.put("CustomerClaim", list) ;
			
			res.setExtData(extData);		
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}
}